package com.lancoo.edu.platform.operation.commons.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.exception.excel.ExcelImportException;
import com.lancoo.edu.platform.operation.commons.exception.ServiceException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;

/**
 * EasyPoi 工具类，导出导入Excel
 * @author pky
 */
@Slf4j
public class EasyPoiUtil {

    public static int USE_SXSSF_LIMIT = 100000;

    /** 允许导出的最大条数 */
    private static final Integer EXPORT_EXCEL_MAX_NUM = 10000;

    public static void sellerExportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    /**
     * 功能描述：复杂导出Excel，包括文件名以及表名。创建表头
     *
     * @param list 导出的实体类
     * @param title 表头名称
     * @param sheetName sheet表名
     * @param pojoClass 映射的实体类
     * @param isCreateHeader 是否创建表头
     * @param fileName 文件名
     * @param response 响应
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * 功能描述：复杂导出Excel，包括文件名以及表名。创建表头
     *
     * @param list 导出的实体类
     * @param title 表头名称
     * @param sheetName sheet表名
     * @param pojoClass 映射的实体类
     * @param isCreateHeader 是否创建表头
     * @param fileName 文件名
     * @param response 响应
     */
    public static void exportExcel(List<?> list, String title, String secondTitle,String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title,secondTitle, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * 功能描述：Map 集合导出
     *
     * @param list 实体集合
     * @param fileName 导出的文件名称
     * @param response 响应
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    /**
     * 功能描述：默认导出方法
     *
     * @param list 导出的实体集合
     * @param fileName 导出的文件名
     * @param boClass Excel 实体
     * @param exportParams ExportParams封装实体
     * @param response 响应
     */
    private static void defaultExport(List<?> list, Class<?> boClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, boClass, list);
        CellStyle cellStyle = workbook.createCellStyle();
        // 水平居左
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 功能描述：默认导出方法
     *
     * @param list 导出的实体集合
     * @param fileName 导出的文件名
     * @param entityList Excel 实体
     * @param exportParams ExportParams封装实体
     * @param response 响应
     */
    private static void defaultExport(List<?> list,List<ExcelExportEntity> entityList, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entityList, list);
        CellStyle cellStyle = workbook.createCellStyle();
        // 水平居左
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 获取导出的 Workbook对象
     *
     * @param title     大标题
     * @param sheetName 页签名
     * @param object    导出实体
     * @param list      数据集合
     * @return Workbook
     */
    public static Workbook getWorkbook(String title, String sheetName, Class<?> object, List<?> list) {
        // 判断导出数据是否为空
        if (list == null) {
            list = new ArrayList<>();
        }
        // 判断导出数据数量是否超过限定值
        if (list.size() > EXPORT_EXCEL_MAX_NUM) {
            title = "导出数据行数超过:" + EXPORT_EXCEL_MAX_NUM + "条,无法导出、请添加导出条件!";
            list = new ArrayList<>();
        }
        // 获取导出参数
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        // 设置导出样式
        exportParams.setStyle(EasyPoiStyleUtil.class);
        // 设置行高
        exportParams.setHeight((short) 8);
        // 输出Workbook流
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, object, list);
        // 设置标题行的行高
        Sheet sheet = workbook.getSheetAt(0);
        Row titleRow = sheet.getRow(0);
        setRowHeight(titleRow);
        // 设置窗口冻结，（0,0）表示不设置冻结
        sheet.createFreezePane(0,0);
        return workbook;
    }

    /**
     * 设置行高
     * @param row 行
     */
    public static void setRowHeight(Row row){
        // 根据内容长度设置行高
        int enterCnt = 0;
        for(int j = 0; j < row.getPhysicalNumberOfCells(); j ++) {
            String s = row.getCell(j).toString();
            int rwsTemp = s.length();
            // 这里取每一行中的每一列字符长度最大的那一列的字符
            if (rwsTemp > enterCnt) {
                enterCnt = rwsTemp;
            }
        }
        // 设置默认行高为 270
        int defaultRowHeight = 270;
        row.setHeightInPoints(Math.min(enterCnt, defaultRowHeight));
    }


    /**
     * 获取导出的 Workbook对象
     *
     * @param path 模板路径
     * @param map  导出内容map
     * @return Workbook
     */
    public static Workbook getWorkbook(String path, Map<String, Object> map) {
        // 获取导出模板
        TemplateExportParams params = new TemplateExportParams(path);
        // 设置导出样式
        params.setStyle(EasyPoiStyleUtil.class);
        // 输出Workbook流
        return ExcelExportUtil.exportExcel(params, map);
    }

    /**
     * 功能描述：Excel导出
     *
     * @param fileName 文件名称
     * @param response 响应
     * @param workbook Excel对象
     */
    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") );
            workbook.write(response.getOutputStream());

        } catch (IOException e) {
            throw new  RuntimeException(e);
        }
    }

    /**
     * 导出Excel
     *
     * @param workbook workbook流
     * @param fileName 文件名
     * @param response 响应
     */
    public static void exportExcel(Workbook workbook, String fileName, HttpServletResponse response) {
        //输出文件
        try {
            //编码
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") );
            //输出表格
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new  RuntimeException(e);
        } finally {
            try {
                if (workbook != null) {
                    //关闭输出流
                    workbook.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 功能描述：默认导出方法
     *
     * @param list 导出的实体集合
     * @param fileName 导出的文件名
     * @param response 响应
     */
    public static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }


    /**
     * 功能描述：根据文件路径来导入Excel
     *
     * @param filePath 文件路径
     * @param titleRows 表标题的行数
     * @param headerRows 表头行数
     * @param boClass Excel 实体类
     * @return Excel 实体类
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> boClass) {
        //判断文件是否存在
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), boClass, params);
        } catch (NoSuchElementException e) {
            throw new RuntimeException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();

        }
        return list;
    }

    /**
     * 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类
     *
     * @param file 上传的文件
     * @param titleRows 表标题的行数
     * @param headerRows 表头行数
     * @param boClass Excel实体类
     * @param needVerify 代表导入这里是需要验证的（根据字段上的注解校验）
     * @return Excel 实体类
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> boClass,boolean needVerify) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        //代表导入这里是需要验证的（根据字段上的注解校验）
        params.setNeedVerify(needVerify);
        List<T> list;
        // 失败结果集
        List<T> failList;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), boClass, params);
        } catch (NoSuchElementException e) {
            log.info("【导入失败】",e);
            throw new ServiceException("excel文件不能为空");
        }catch (ExcelImportException e){
            // 在类属性上添加：isImportField = "true"
            log.info("【导入失败】",e);
            throw new ServiceException("导入失败，请导入数据正确的模板");
        } catch (Exception e) {
            log.info("【导入失败】",e);
            throw new ServiceException("导入失败，请导入数据的模板");
        }
        return list;
    }

    /**
     * 构建 sheet
     *
     * @param sheetName sheet 名字
     * @param clazz     clazz
     * @param list      列表
     * @return {@link Map<String, Object>}
     */
    public static Map<String, Object> buildSheet(String sheetName, String title, Class<?> clazz, List<?> list) {
        ExportParams exportParams = new ExportParams();
        exportParams.setType(ExcelType.HSSF);
        exportParams.setSheetName(sheetName);
        exportParams.setTitle(title);
        // 导出样式
        exportParams.setStyle(EasyPoiStyleUtil.class);
        Map<String, Object> map = new HashMap<>(4);
        // title的参数为ExportParams类型
        map.put("title", exportParams);
        // 模版导出对应得实体类型
        map.put("entity", clazz);
        // sheet中要填充得数据
        map.put("data", list);
        return map;
    }
}